<?php
class ChiTieuPhanLoai extends AppModel{
	var $name = 'ChiTieuPhanLoai';
	
	public function getBaoCaoPL($from, $to, $tthc_id) {
		$sql = "select * from chi_tieu_phan_loais ct
				left join (
					select ct.id, sum(pl.so_lieu) as dauky from chi_tieu_phan_loais ct 
					left join phan_loai_hosos pl on ct.id = pl.chi_tieu_phan_loai_id 
					where pl.created <= DATE('".$from."')
					group by ct.id
				) dauky on ct.id = dauky.id
				left join (
					select ct.id, sum(pl.so_lieu) as trongky from chi_tieu_phan_loais ct 
					left join phan_loai_hosos pl on ct.id = pl.chi_tieu_phan_loai_id 
					where pl.created between DATE('".$from."') and DATE('".$to."') 
					group by ct.id
				) trongky on ct.id = trongky.id
				left join (
					select ct.id, sum(pl.so_lieu) as cuoiky from chi_tieu_phan_loais ct 
					left join phan_loai_hosos pl on ct.id = pl.chi_tieu_phan_loai_id 
					where pl.created > DATE('".$to."') 
					group by ct.id
				) cuoiky on ct.id = cuoiky.id";
			
			if(empty($tthc_id)) {
				$sql .= "pl.tthc_id = $tthc_id";
			}
		// debug($sql);
		return $this->query($sql);
	}
	
	public function getSolieuPhanloaiByKqhc($kqhcId = null) {
		$sql = "select * from chi_tieu_phan_loais pl 
			left join (select * from phan_loai_hosos where ketquahanhchinh_id = $kqhcId) 
			pl_hs on pl_hs.chi_tieu_phan_loai_id = pl.id  group by pl.id";
		
		return $this->query($sql);
	}
}